Syntactical transformation of database interaction statements

ABSTRACT

A computer-implemented method includes identifying a primary database interaction statement and determining one or more intermediate database interaction statements based on the primary database interaction statement. The computer-implemented further includes determining, for each of the one or more intermediate database interaction statements, an intermediate statement transformation indicator. The computer-implemented method further includes determining one or more transformed database interaction statements based on each intermediate statement transformation indicator. A corresponding computer program product and computer system are also disclosed.

BACKGROUND

The present invention relates generally to the field of databasesystems, and more particularly to syntactical transformation of databaseinteraction statements.

In the context of database systems, the structure of databaseinteraction statements can have consequence for database performance.Inefficient statement structures negatively affect user experience wheninteracting with a database system. Developers and users of databasesystems continue to have difficulties with the quality of service onthose systems.

SUMMARY

A computer-implemented method includes identifying a primary databaseinteraction statement and determining one or more intermediate databaseinteraction statements based on the primary database interactionstatement. The computer-implemented further includes determining, foreach of the one or more intermediate database interaction statements, anintermediate statement transformation indicator. Thecomputer-implemented method further includes determining one or moretransformed database interaction statements based on each intermediatestatement transformation indicator. A corresponding computer programproduct and computer system are also disclosed.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a block diagram of one embodiment of a computer systemenvironment suitable for operation of a database statementtransformation program, in accordance with at least one embodiment ofthe present invention.

FIG. 2 is a data flow diagram of a database statement transformationprogram, in accordance with at least one embodiment of the presentinvention.

FIG. 3 is a flowchart diagram of a database statement transformationprogram, in accordance with at least one embodiment of the presentinvention.

FIG. 4 is a block diagram of a computing apparatus suitable forexecuting a database statement transformation program, in accordancewith at least one embodiment of the present invention.

DETAILED DESCRIPTION

FIG. 1 is a computer system environment 100 suitable for operating adatabase statement transformation program 110, in accordance with atleast one embodiment of the present invention. In the embodimentdepicted in FIG. 1, the program 110 uses information artefacts providedby a transformation modeling unit 121 and a database developmentplatform 122 to determine one or more transformed database interactionstatements 199. In at least some embodiments, a transformation modelingunit 121 is any combination of one or more computer (hardware orsoftware components) that, in whole or in part, provide at least oneinformation artefact about how to transform a database interactionstatement (e.g., at least one information artefact about atransformation eligibility pattern, an intermediate statement evaluationguideline, and/or a transformed statement generation guideline). In atleast some embodiments, a database interaction statement is any computerstatement that can, during regular and non-exceptional executions, beused to do at least one of selecting, inserting, deleting, and updatingdata in a database system. Examples of database interaction statementsinclude the following statements in a structured query language (SQL):SELECT, UPDATE, MERGE, INSERT, and DELETE. In at least some embodiments,a database interaction statement is a statement in a databasemanipulation language. In at least some embodiments, a databasedevelopment platform 122 is any combination of one or more computer(hardware or software components) that, in whole or in part and duringregular and non-exceptional executions, communicate at least onedatabase interaction statement to the database transformation program110 and/or to at least one database management system.

In the computer system environment 100 depicted in FIG. 1, a transformeddatabase interaction statement 199 is any combination of one or moredata artefacts that, directly or when interpreted along with other oneor more data artefacts, indicate at least one information artefact aboutat least one database interaction statement after one or moredeterminations and/or transformations made by the database statementtransformation program 110 on the at least one database interactionstatement. In at least some embodiments, the one or more determinationsand/or transformations made by the program 110 on the at least onedatabase interaction statement are based on at least one property (e.g.,the output of execution) associated with an intermediate databaseinteraction statement determined based on the at least one databaseinteraction statement.

FIG. 2 is a data-flow diagram of a database statement transformationprogram 110, in accordance with at least one embodiment of the presentinvention. In the embodiment depicted in FIG. 2, the program 110receives a primary database interaction statement (i.e., a databaseinteraction statement before any syntactical transformation by theprogram 110) from the database development platform 122. The program 110uses the primary database interaction statement 132 and a transformationeligibility pattern 131 to determine a transformation eligibilityindicator 171 associated with the primary database interaction statement132. In at least some embodiments, a transformation eligibility pattern131 is any combination of one or more data artefacts that, directly orwhen interpreted along with other one or more data artefacts, indicateat least one information artefact about when the primary databaseinteraction statement 132 is eligible for syntactical transformation. Inat least some embodiments, a transformation eligibility indicator 171 isany combination of one or more data artefacts that, directly or wheninterpreted along with other one or more data artefacts, indicate atleast one information artefact about whether the primary databaseinteraction statement 132 complies with at least one transformationeligibility pattern 131 (e.g., based on at least one guideline aboutwhen a database interaction statement complies with a particulartransformation eligibility pattern 131). In some embodiments, atransformation eligibility pattern 131 indicates the following databasestatement pattern (the “sum-constant pattern”):

 A database interaction statement complying with the syntacticalpattern:   SELECT ...   [CASE WHEN COLUMN = CONSTANT THEN JUMP_FACTOR_1ELSE   JUMP_FACTOR_2 END,]_(n)   WHERE ... COLUMN = DETERMINANT_FACTOR  FROM TABLE; Wherein DETERMINANT_FACTOR is either aCONSTANT_RETURNING_FUNCTION OR a CONSTANT_VALUE

Pattern 1

In the database statement pattern in Pattern 1, COLUMN represents aparticular column in the table TABLE. CONSTANT represents any constantvalue and Jump_Factor_1 and Jump_Factor_2 are constant or variablefactors. The CONSTANT_RETURNING_FUNCTION is a function that duringregular and non-exceptional executions always results in a constantvalue and the CONSTANT_VALUE is a constant value. In at least someembodiments, a database interaction statement complies with the databasestatement pattern in Pattern 1 when it comprises: (i) one or more CASEpredicates testing for the equality of a column and a constant value;and (ii) a WHERE predicate that tests whether the column is equal toeither a constant returning function or a constant value.

In an exemplary embodiment, the following database interaction statementcomplies with the database statement pattern in Pattern 1:

 SELECT COUNT (*),  SUM(CASE WHEN WHRG_SCHL_ISO = X′202020′ THEN 1 ELSE0  END) AS X20, SUM(CASE WHEN WHRG_SCHL_ISO = X′404040′ THEN 2 ELSE 0END) AS X40, FROM T1 WHERE WHRG_SCHL_ISO = SUBSTR(X′4040404040′,1,3);

Example 1

In the embodiment depicted in FIG. 2, if the database statementtransformation program 110 determines that the primary databaseinteraction statement is eligible for at least one syntacticaltransformation (e.g., if the program 110 determines that the primarydatabase interaction statement complies with at least one transformationeligibility pattern 131), the program 110 determines one or moreintermediate database interaction statements 181 based on the primarydatabase interaction statement 132 based on one or more intermediatestatement generation guidelines 141. In at least some embodiments, anintermediate statement generation guideline 141 is any combination ofone or more data artefacts that, directly or when interpreted along withother one or more data artefacts, indicate at least one informationartefact about how at least one intermediate database interactionstatement 181 should be determined based on the primary databaseinteraction statement 132. In at least some embodiments, an intermediatestatement generation guideline 141 is determined based on anintermediate statement pattern, wherein the intermediate statementpattern comprises at least one information artefact about thesyntactical form of at least one intermediate statement generationguideline 141. In at least some embodiments, an intermediate databaseinteraction statement 181 is any database interaction statementdetermined based on the primary database interaction statement 132,wherein at least one transformed database interaction statement 199 canbe determined based on at least one property (e.g., output of execution)associated with the intermediate database interaction statement 181. Insome embodiments, the one or more intermediate statement generationguidelines 141 comprise the following guideline (the

“constant-function intermediate statement generation guideline”):   IfDETERMINANT_FACTOR = CONSTANT_RETURNING_FUNCTION, then, for each Constant in a CASE predicate, determine the intermediate databaseinteraction  statement:    SELECT CASE WHERE CONSTANT =  CONSTANT_RETURNING_FUNCTION Then 1 ELSE 0 END FROM TABLE;

Guideline 1

Based on the intermediate statement generation guideline 141 inGuideline 1, if the DETERMINANT_FACTOR is a CONSTANT_RETURNING_FUNCTION,then the intermediate database interaction statement 181 must complywith the following intermediate statement pattern (the“constant-function intermediate statement pattern”): SELECT CASE WHENCONSTANT=CONSTANT_RETURNING_FUNCTION Then 1 ELSE 0 END FROM TABLE. Insome embodiments, the constant-function intermediate pattern comprises:(i) a SELECT statement with a CASE predicate, wherein the case predicateincludes a WHEN predicate; and (ii) the WHEN predicate tests for theequality of a constant value in a statement complying with the sumconstant pattern with a constant returning function in the statementcomplying with the sum constant pattern; and (iii) the potentialincrementing of the value of a jump factor based on the result of theaforementioned equality test.

In some embodiments, the one or more intermediate statement generationguidelines 141 comprise the following guidelines (the “constant-literalintermediate statement generation guideline”):

 If DETERMINANT_FACTOR = CONSTANT_VALUE, then determine the followingintermediate database interaction statement:   SELECT 1 WHERE CONSTANT =CONSTANT_VALUE FROM TABLE;

Guideline 2

Based on the intermediate statement generation guideline 141 inGuideline 2, if the DETERMINANT_FACTOR is a CONSTANT_VALUE, then theintermediate database interaction statement 181 must comply with thefollowing intermediate statement pattern (the “constant-literalintermediate statement pattern”): SELECT 1 WHERE CONSTANT=CONSTANT_VALUEFROM TABLE. In some embodiments, the constant-literal intermediatestatement pattern comprises a SELECT statement that includes a number 1as its sole parameter and includes a WHERE predicate, wherein the WHEREpredicate tests for the equality of a constant value in a statementcomplying with the sum constant pattern with another constant value inthe statement complying with the sum constant pattern.

In an exemplary embodiment, the following two intermediate databaseinteraction statements 181 are generated based on the primary databaseinteraction statement 132 in Example 1 and the constant-functionintermediate statement generation guideline presented in Guideline 2:

 SELECT CASE WHEN X′202020′=SUBSTR(X′4040404040′,1,3) THEN 1 ELSE 0 END  FROM T1;  SELECT CASE WHEN X′404040′=SUBSTR(X′4040404040′,1,3) THEN 1ELSE 0 END   FROM T1;

Example 2

In the embodiment depicted in FIG. 2, the database statementtransformation program 110 uses at least one property (e.g., output ofexecution) associated with at least one intermediate databaseinteraction statement 181 and one or more intermediate statementevaluation guidelines 151 to determine at least one intermediatestatement transformation indicator 191 associated with the at least oneintermediate database interaction statement 181. In at least someembodiments, an intermediate statement evaluation guideline 151 is anycombination of one or more data artefacts that, directly or wheninterpreted along with other one or more data artefacts, indicate how tointerpret at least one property (e.g., output of execution) associatedwith the with at least one intermediate database interaction statement181 to determine at least one at least one intermediate statementtransformation indicator 191 associated with the at least oneintermediate database interaction statement 181. In at least someembodiments, an intermediate statement transformation indicator 191associated with at least one intermediate database interaction statement181 is any combination of one or more data artefacts that, directly orwhen interpreted along with other one or more data artefacts, indicateat least one information artefact about how to determine at least onetransformed database interaction statement 199 based on the at least oneintermediate database interaction statement 181. In some embodiments,the one or more intermediate statement evaluation guidelines 151comprise the following guideline (the “count-constant intermediatestatement evaluation guideline”):

-   -   If executing the intermediate database interaction statement        produces the output 1, determine a transformed database        interaction statement based on a count-constant transformed        statement pattern.

Guideline 3

In some embodiments, the one or more intermediate statement evaluationguidelines 151 comprise the following guideline (the “zero-constantintermediate statement evaluation guideline”):

-   -   If executing the intermediate database interaction statement        produces the output 0, determine a transformed database        interaction statement based on a zero-constant transformed        statement pattern.

Guideline 4

In the embodiment depicted in FIG. 2, the database statementtransformation program 110 uses the one or more intermediate statementtransformation indicators 191 and the one or more transformed statementgeneration patterns 161 to determine at least one transformed databaseinteraction statement 199. In at least some embodiments, a transformedstatement generation pattern 161 is any combination of one or more dataartefacts that, directly or when interpreted along with other one ormore data artefacts, indicate at least one information artefact about atleast one syntactical property associated with at least one transformeddatabase interaction statement 199. In some embodiments, the one or moretransformed statement patterns comprise the following pattern (the“count-constant transformed statement pattern”):

-   -   SELECT COUNT (*) * Jump_Factor_1;

Pattern 2

In some embodiments, the count-constant transformed statement patterncomprises a SELECT statement with a parameter comprising themultiplication of COUNT (*) and a jump factor in a statement complyingwith the sum constant pattern. In some embodiments, the one or moretransformed statement patterns comprise the following pattern (the“zero-constant transformed statement pattern”):

-   -   SELECT 0;

Pattern 3

In some embodiments, the zero-constant transformed statement patterncomprises a SELECT statement with number 0 as a parameter.

In an exemplary embodiment, the database statement transformationprogram 110 determines the following transformed database interactionstatement 199 based on the primary database modification statement 132in Example 1, the intermediate database interaction statements 181 inExample 2, and the intermediate statement evaluation guideline inGuidelines 3 and 4:

SELECT COUNT (*),  0 AS X20,  COUNT (*) *2 AS X40,     FROM T1    WHEREWHRG_SCHL_ISO = X′404040′;

Example 3

In the embodiment depicted in Example 3, the portion of the statement “0AS X20” is determined based on the zero-constant transformed statementpattern, while the portion of the statement “COUNT (*) * 2 AS X40” isdetermined based on the count-constant transformed statement pattern.

FIG. 3 is a flow-chart diagram of a database statement transformationprogram, in accordance with at least one embodiment of the presentinvention. At step 300, the program identifies a primary databaseinteraction statement. At step 301, the program determines one or moreintermediate database interaction statements based on the primarydatabase interaction statement. At step 302, the program determines, foreach of the one or more intermediate database interaction statements, anintermediate statement transformation indicator. At step 303, theprogram determines one or more or more transformed database interactionstatements based on each intermediate statement transformationindicator.

The database statement transformation program identifies the primarydatabase interaction statement at step 300. In some embodiments, theprogram identifies the primary database interaction statement based onat least one information artefact received from a database developmentplatform. In some embodiments, the program periodically scans one ormore data artefacts associated with the database development platform(e.g., through an application programming interface provided by thedatabase development platform) to identify the primary databaseinteraction statement. In some embodiments, the database developmentplatform communicates an indication of the primary database interactionstatement to the program. In some embodiments, the program identifiesthe primary database interaction statement based on at least oneinformation artefact received from at least one user of the computersystem environment comprising the program. In some embodiments, theprimary database interaction statement is a select query in a structuredquery language.

The database statement transformation program determines one or moreintermediate database interaction statements at step 301. In someembodiments, the program determines the one or more intermediatedatabase interaction statements based on one or more intermediatestatement generation guidelines. In some embodiments, the programdetermines the one or more intermediate database interaction statementsbased on at least one information artefact received from a databasedevelopment platform. In some embodiments, the program determines theone or more intermediate database interaction statements based on atleast one information artefact received from at least one user of thecomputer system environment comprising the program. In some embodiments,the primary database interaction statement is a select query in astructured query language.

In some embodiments, the database statement transformation programfurther comprises determining a transformation eligibility indicatorassociated with the primary database interaction statement; anddetermining the one or more intermediate database interaction statementsis performed responsive to the transformation eligibility indicatorindicating that the primary database interaction statement is eligiblefor at least one syntactical transformation. In some embodiments,determining the transformation eligibility indicator further comprisesidentifying one or more transformation eligibility patterns; anddetermining whether the primary modification statement complies with atleast one of the one or more transformation eligibility patterns. Insome embodiments, the one or more transformation eligibility patternscomprises a sum-constant pattern; each of the one or more intermediatedatabase interaction statements are determined based on least oneintermediate statement pattern selected from the group consisting of: aconstant-function intermediate statement pattern and a constant-literalintermediate statement pattern; and each of the one or more transformeddatabase interaction statements are determined based on least onetransformed statement patterns selected from the group consisting of: acount-constant transformed statement pattern and a zero-constanttransformed statement pattern. In some embodiments, at least one of saidone or more transformation eligibility patterns comprise the followingsyntactical pattern:

SELECT ... [CASE WHEN COLUMN = CONSTANT THEN JUMP_FACTOR_1 ELSEJUMP_FACTOR_2 END,]_(n) WHERE ... COLUMN = DETERMINANT_FACTOR FROMTABLE.

The database statement transformation program determines eachintermediate statement transformation indicator at step 302. In someembodiments, the program determines each intermediate statementtransformation indicator based on at least one intermediate statementevaluation guideline. In some embodiments, the program determines eachintermediate statement transformation indicator based on at least oneinformation artefact received from a database development platform. Insome embodiments, the program determines each intermediate statementtransformation indicator based on at least one information artefactreceived from at least one user of the computer system environmentcomprising the program.

In some embodiments, the database statement transformation programfurther comprises identifying, for each of the one or more intermediatedatabase interaction statements, an intermediate output data segment andidentifying one or more intermediate statement evaluation guidelines. Inat least some of those embodiments, determining each intermediatestatement transformation indicator is performed based on eachintermediate output data segment and the one or more intermediatestatement evaluation guidelines. In some embodiments, the one or moreintermediate statement evaluation guidelines are selected from the groupconsisting of: a count-constant intermediate statement evaluationguideline and a zero-constant intermediate statement evaluationguideline. In at least some embodiments, an intermediate output datasegment associated with an intermediate database interaction statementis any combination of one or more data artefacts that, directly or wheninterpreted with one or more other data artefacts, indicate at least oneinformation artefact about the result and/or output of execution of theintermediate database interaction statement at one time during regularand non-exceptional executions. In at least some embodiments, a datasegment is any combination of one or more data artefacts, whether or notthose one or more data artefacts are stored contiguously or not.

The database statement transformation program determines the one or moretransformed database interaction statements at step 303. In someembodiments, the program determines the one or more transformed databaseinteraction statements based on based on at least one informationartefact received from at least one user of the computer systemenvironment comprising the program. In some embodiments, the programdetermines the one or more transformed database interaction statementsbased on at least one information artefacts estimating the speed and/orother execution-related property of each transformed databaseinteraction statements. In some embodiments, the program determines theone or more transformed database interaction statements based on atleast transformed statement generation guideline.

In some embodiments, the database statement transformation programoperates as part of a software development platform for executingdatabase interaction statements, such as an optimization unit within aquery compiler in a database management system. In some otherembodiments, the database statement transformation program operates as astand-alone software platform with which at least one other computer(hardware or software) component and/or at least one computer userinteract to determine at least one syntactical transformation of atleast one database interaction statement.

Embodiments of the present invention enable transformation of databaseinteraction statements in a manner that enhances their performanceand/or simplicity. The inventors have recognized a general technique forsuch transformation as well as the guidelines and patterns needed for anexample of such transformation. The inventors have recognized that,through such transformations, the performance and/or simplicity ofdatabase interaction statements can be increased. Nevertheless, theaforementioned advantages are not required to be present in all of theembodiments of the invention and may not be present in all of theembodiments of the invention.

In general, one or more steps associated with different embodiments ofthe database statement transformation program may be performed based onone or more pieces of information obtained directly or indirectly fromone or more computer (hardware or software) components, one or morepieces of information obtained directly or indirectly from one or moreinputs from one or more users, and/or one or more observed behaviorsassociated with one or more (hardware or software) components of one ormore computer system environments. In general, one or more steps ofdifferent embodiments of the database statement transformation programmay comprise communicating with one or more computer (hardware orsoftware) components, issuing one or more computer instructions (e.g.,one or more special purpose machine-level instructions defined in theinstruction set of one or more computer hardware components), and/orcommunicating with one or more computer components at the hardwarelevel.

FIG. 4 is a block diagram depicting components of a computer 400suitable for executing the database statement transformation program.FIG. 4 displays the computer 400, the one or more processor(s) 404(including one or more computer processors), the communications fabric402, the memory 406, the RAM, the cache 416, the persistent storage 408,the communications unit 410, the I/O interfaces 412, the display 420,and the external devices 418. It should be appreciated that FIG. 4provides only an illustration of one embodiment and does not imply anylimitations with regard to the environments in which differentembodiments may be implemented. Many modifications to the depictedenvironment may be made.

As depicted, the computer 400 operates over a communications fabric 402,which provides communications between the cache 416, the computerprocessor(s) 404, the memory 406, the persistent storage 408, thecommunications unit 410, and the input/output (I/O) interface(s) 412.The communications fabric 402 may be implemented with any architecturesuitable for passing data and/or control information between theprocessors 404 (e.g., microprocessors, communications processors, andnetwork processors, etc.), the memory 406, the external devices 418, andany other hardware components within a system. For example, thecommunications fabric 402 may be implemented with one or more buses or acrossbar switch.

The memory 406 and persistent storage 408 are computer readable storagemedia. In the depicted embodiment, the memory 406 includes a randomaccess memory (RAM). In general, the memory 406 may include any suitablevolatile or non-volatile implementations of one or more computerreadable storage media. The cache 416 is a fast memory that enhances theperformance of computer processor(s) 404 by holding recently accesseddata, and data near accessed data, from memory 406.

Program instructions for the database statement transformation programmay be stored in the persistent storage 408 or in memory 406, or moregenerally, any computer readable storage media, for execution by one ormore of the respective computer processors 404 via the cache 416. Thepersistent storage 408 may include a magnetic hard disk drive.Alternatively, or in addition to a magnetic hard disk drive, thepersistent storage 408 may include, a solid state hard disk drive, asemiconductor storage device, read-only memory (ROM), electronicallyerasable programmable read-only memory (EEPROM), flash memory, or anyother computer readable storage media that is capable of storing programinstructions or digital information.

The media used by the persistent storage 408 may also be removable. Forexample, a removable hard drive may be used for persistent storage 408.Other examples include optical and magnetic disks, thumb drives, andsmart cards that are inserted into a drive for transfer onto anothercomputer readable storage medium that is also part of the persistentstorage 408.

The communications unit 410, in these examples, provides forcommunications with other data processing systems or devices. In theseexamples, the communications unit 410 may include one or more networkinterface cards. The communications unit 410 may provide communicationsthrough the use of either or both physical and wireless communicationslinks. The database statement transformation program may be downloadedto the persistent storage 408 through the communications unit 410. Inthe context of some embodiments of the present invention, the source ofthe various input data may be physically remote to the computer 400 suchthat the input data may be received and the output similarly transmittedvia the communications unit 410.

The I/O interface(s) 412 allows for input and output of data with otherdevices that may operate in conjunction with the computer 400. Forexample, the I/O interface 412 may provide a connection to the externaldevices 418, which may include a keyboard, keypad, a touch screen,and/or some other suitable input devices. External devices 418 may alsoinclude portable computer readable storage media, for example, thumbdrives, portable optical or magnetic disks, and memory cards. Softwareand data used to practice embodiments of the present invention may bestored on such portable computer readable storage media and may beloaded onto the persistent storage 408 via the I/O interface(s) 412. TheI/O interface(s) 412 may similarly connect to a display 420. The display420 provides a mechanism to display data to a user and may be, forexample, a computer monitor.

The programs described herein are identified based upon the applicationfor which they are implemented in a specific embodiment of theinvention. However, it should be appreciated that any particular programnomenclature herein is used merely for convenience, and thus theinvention should not be limited to use solely in any specificapplication identified and/or implied by such nomenclature.

The present invention may be a system, a method, and/or a computerprogram product at any possible technical detail level of integration.The computer program product may include a computer readable storagemedium (or media) having computer readable program instructions thereonfor causing a processor to carry out aspects of the present invention.

The computer readable storage medium can be a tangible device that canretain and store instructions for use by an instruction executiondevice. The computer readable storage medium may be, for example, but isnot limited to, an electronic storage device, a magnetic storage device,an optical storage device, an electromagnetic storage device, asemiconductor storage device, or any suitable combination of theforegoing. A non-exhaustive list of more specific examples of thecomputer readable storage medium includes the following: a portablecomputer diskette, a hard disk, a random access memory (RAM), aread-only memory (ROM), an erasable programmable read-only memory (EPROMor Flash memory), a static random access memory (SRAM), a portablecompact disc read-only memory (CD-ROM), a digital versatile disk (DVD),a memory stick, a floppy disk, a mechanically encoded device such aspunch-cards or raised structures in a groove having instructionsrecorded thereon, and any suitable combination of the foregoing. Acomputer readable storage medium, as used herein, is not to be construedas being transitory signals per se, such as radio waves or other freelypropagating electromagnetic waves, electromagnetic waves propagatingthrough a waveguide or other transmission media (e.g., light pulsespassing through a fiber-optic cable), or electrical signals transmittedthrough a wire.

Computer readable program instructions described herein can bedownloaded to respective computing/processing devices from a computerreadable storage medium or to an external computer or external storagedevice via a network, for example, the Internet, a local area network, awide area network and/or a wireless network. The network may comprisecopper transmission cables, optical transmission fibers, wirelesstransmission, routers, firewalls, switches, gateway computers and/oredge servers. A network adapter card or network interface in eachcomputing/processing device receives computer readable programinstructions from the network and forwards the computer readable programinstructions for storage in a computer readable storage medium withinthe respective computing/processing device.

Computer readable program instructions for carrying out operations ofthe present invention may be assembler instructions,instruction-set-architecture (ISA) instructions, machine instructions,machine dependent instructions, microcode, firmware instructions,state-setting data, configuration data for integrated circuitry, oreither source code or object code written in any combination of one ormore programming languages, including an object oriented programminglanguage such as Smalltalk, C++, or the like, and procedural programminglanguages, such as the “C” programming language or similar programminglanguages. The computer readable program instructions may executeentirely on the user's computer, partly on the user's computer, as astand-alone software package, partly on the user's computer and partlyon a remote computer or entirely on the remote computer or server. Inthe latter scenario, the remote computer may be connected to the user'scomputer through any type of network, including a local area network(LAN) or a wide area network (WAN), or the connection may be made to anexternal computer (for example, through the Internet using an InternetService Provider). In some embodiments, electronic circuitry including,for example, programmable logic circuitry, field-programmable gatearrays (FPGA), or programmable logic arrays (PLA) may execute thecomputer readable program instructions by utilizing state information ofthe computer readable program instructions to personalize the electroniccircuitry, in order to perform aspects of the present invention.

Aspects of the present invention are described herein with reference toflowchart illustrations and/or block diagrams of methods, apparatus(systems), and computer program products according to embodiments of theinvention. It will be understood that each block of the flowchartillustrations and/or block diagrams, and combinations of blocks in theflowchart illustrations and/or block diagrams, can be implemented bycomputer readable program instructions.

These computer readable program instructions may be provided to aprocessor of a general purpose computer, special purpose computer, orother programmable data processing apparatus to produce a machine, suchthat the instructions, which execute via the processor of the computeror other programmable data processing apparatus, create means forimplementing the functions/acts specified in the flowchart and/or blockdiagram block or blocks. These computer readable program instructionsmay also be stored in a computer readable storage medium that can directa computer, a programmable data processing apparatus, and/or otherdevices to function in a particular manner, such that the computerreadable storage medium having instructions stored therein comprises anarticle of manufacture including instructions which implement aspects ofthe function/act specified in the flowchart and/or block diagram blockor blocks.

The computer readable program instructions may also be loaded onto acomputer, other programmable data processing apparatus, or other deviceto cause a series of operational steps to be performed on the computer,other programmable apparatus or other device to produce a computerimplemented process, such that the instructions which execute on thecomputer, other programmable apparatus, or other device implement thefunctions/acts specified in the flowchart and/or block diagram block orblocks.

The flowchart and block diagrams in the Figures illustrate thearchitecture, functionality, and operation of possible implementationsof systems, methods, and computer program products according to variousembodiments of the present invention. In this regard, each block in theflowchart or block diagrams may represent a module, segment, or portionof instructions, which comprises one or more executable instructions forimplementing the specified logical function(s). In some alternativeimplementations, the functions noted in the blocks may occur out of theorder noted in the Figures. For example, two blocks shown in successionmay, in fact, be executed substantially concurrently, or the blocks maysometimes be executed in the reverse order, depending upon thefunctionality involved. It will also be noted that each block of theblock diagrams and/or flowchart illustration, and combinations of blocksin the block diagrams and/or flowchart illustration, can be implementedby special purpose hardware-based systems that perform the specifiedfunctions or acts or carry out combinations of special purpose hardwareand computer instructions.

What is claimed is:
 1. A computer program product, the computer programproduct comprising: one or more computer readable storage media; andcomputer code stored on said one or more computer readable storagemedia, with the computer code including instructions for causing aprocessor(s) set to perform operations including the following:receiving a primary database interaction statement in SQL, determiningthat the primary database interaction statement includes at least oneSQL case statement portion, including a first SQL case statementportion, determining that the first SQL case statement portion iseligible for an SQL query optimization transformation based, at least inpart, on comparing the first SQL case statement portion against a set ofSQL transformation patterns, where a first SQL transformation pattern isan SQL case statement which evaluates an equation with aconstant-returning function on one side of the equation, and a secondSQL transformation pattern is an SQL case statement which evaluates anequation with a constant value on one side of the equation, responsiveto determining that the first SQL case statement portion comparisonyielded a match with the first SQL transformation pattern, generating,by machine logic, a first set of intermediate SQL database interactionstatements based, at least in part, on the first SQL case statementportion and a set of SQL intermediate statement guidelines correspondingto the set of SQL transformation patterns, where the set of SQLintermediate statement guidelines includes at least a first SQLintermediate statement guideline corresponding to the first SQLtransformation pattern, where the first SQL intermediate statementguideline stipulates that, for each given constant in the first SQL casestatement portion, generate a select case statement which returns aninteger value of 1 if the given constant is equal to output of theconstant-returning function and a value of 0 if they are not equal,responsive to generating at least one SQL intermediate statement basedupon the first SQL intermediate statement guideline and the first SQLcase statement portion, for each given SQL intermediate statement of thefirst set of intermediate SQL interaction statements, determining, bymachine logic, the results of the given SQL intermediate statement ofthe first set of intermediate SQL interaction statements, selecting, bymachine logic, a first SQL intermediate statement transformationindicator from a set of SQL intermediate statement indicators based, atleast in part, on the determined results of at least one SQLintermediate statement of the first set of intermediate SQL interactionstatements, where the first SQL intermediate statement transformationindicator indicates replacing the first SQL case statement portion ofthe primary database interaction statement with a count functionmultiplied by a numerical value, where the numerical value is based uponthe determined results of the at least one SQL intermediate statement ofthe first set of intermediate SQL interaction statements, andtransforming the primary database interaction statement into atransformed database interaction, where the transformed databaseinteractions statement includes replacement of the first SQL casestatement portion of the primary database interaction statement with afirst transformed SQL query portion that is based on the selected firstSQL intermediate transformation indicator, where execution of thetransformed database interaction statement requires reduced computationresources relative to the primary database interaction statement.
 2. Thecomputer program product of claim 1, wherein the computer code furtherincludes instructions for causing the processor(s) set to perform thefollowing operations: communicating the transformed database interactionstatement to a database system in place of the primary databaseinteraction statement.
 3. The computer program product of claim 2,wherein the computer code further includes instructions for causing theprocessor(s) set to perform the following operations: performing, by thedatabase system, the transformed database interaction statement on adatabase.
 4. The computer program product of claim 1, wherein: primarydatabase interaction statement includes a second SQL case statementportion; and the transformed database interaction statement includes thesecond SQL case statement portion.
 5. The product of claim 4, whereinthe set of SQL intermediate statement guidelines corresponding to theset of SQL transformation patterns includes a second SQL intermediatestatement guideline corresponding to the second SQL transformationpattern; and the second SQL intermediate statement guideline stipulatesthat, for each given constant in the second SQL case statement portion,generate a select statement which returns an integer value of 1 if thegiven constant is equal to output of the constant value.
 6. The productof claim 5, wherein the computer code further includes instructions forcausing the processor(s) set to perform the following operations:determining that the second SQL case statement portion is eligible foran SQL query optimization transformation based, at least in part, oncomparing the second SQL case statement portion against the set of SQLtransformation patterns; and responsive to determining that the secondSQL case statement portion comparison yielded a match with the secondSQL transformation pattern, generating, by machine logic, a second setof intermediate SQL database interaction statements based, at least inpart, on the second SQL case statement portion and the set of SQLintermediate statement guidelines corresponding to the set of SQLtransformation patterns; responsive to generating at least one SQLintermediate statement based upon the second SQL intermediate statementguideline and the second SQL case statement portion, for each given SQLintermediate statement of the second set of intermediate SQL databaseinteraction statements, determining, by machine logic, the results ofthe given SQL intermediate statement of the second set of intermediateSQL database interaction statements; selecting, by machine logic, asecond SQL intermediate statement transformation indicator from the setof SQL intermediate statement indicators based, at least in part, on thedetermined results of at least one SQL intermediate statement of thesecond set of intermediate SQL database interaction statements, wherethe second SQL intermediate statement transformation indicator indicatesreplacing the second SQL case statement portion of the transformeddatabase interaction statement with a count function multiplied by anumerical value, where the numerical value is based upon the determinedresults of the at least one SQL intermediate statement based upon thesecond SQL intermediate statement guideline, and transforming thetransformed database interaction statement into a second transformeddatabase interaction, where the second transformed database interactionsstatement includes replacement of the second SQL case statement portionof the transformed database interaction statement with a secondtransformed SQL query portion that is based on the selected second SQLintermediate transformation indicator, where execution of the secondtransformed database interaction statement requires reduced computationresources relative to the primary database interaction statement and thetransformed database interaction statement.
 7. The product of claim 6,wherein: the primary database interaction statement includes a third SQLcase statement portion; and the second transformed database interactionstatement includes the third SQL case statement portion.
 8. The productof claim 7, wherein the computer code further includes instructions forcausing the processor(s) set to perform the following operations:determining that the third SQL case statement portion is eligible for anSQL query optimization transformation based, at least in part, oncomparing the third SQL case statement portion against the set of SQLtransformation patterns; and responsive to determining that the thirdSQL case statement portion comparison yielded a match with at least oneSQL transformation pattern, generating, by machine logic, a third set ofintermediate SQL database interaction statements based, at least inpart, on the third SQL case statement portion and the set of SQLintermediate statement guidelines corresponding to the set of SQLtransformation patterns; responsive to generating at least one SQLintermediate statement based upon at least one SQL intermediatestatement guideline and the third SQL case statement portion, for eachgiven SQL intermediate statement the third set of intermediate SQLdatabase interaction statements, determining, by machine logic, theresults of the given SQL intermediate statement of the third set ofintermediate SQL database interaction statements; responsive todetermining that the results of the third set of intermediate SQLdatabase interaction statements indicates a numerical value of zero,selecting, by machine logic, a third SQL intermediate statementtransformation indicator from the set of SQL intermediate statementindicators based, at least in part, on the determined results of atleast one intermediate SQL database interaction statement of the thirdset of intermediate SQL database interaction statements, where the thirdSQL intermediate statement transformation indicator indicates replacingthe third SQL case statement portion of the second transformed databaseinteraction statement with a numerical value of zero, and transformingthe second transformed database interaction statement into a thirdtransformed database interaction, where the third transformed databaseinteractions statement includes replacement of the third SQL casestatement portion of the second transformed database interactionstatement with a third transformed SQL query portion that is based onthe selected third SQL intermediate transformation indicator, whereexecution of the third transformed database interaction statementrequires reduced computation resources relative to: (i) the primarydatabase interaction statement, (ii) the transformed databaseinteraction statement, and (iii) the second transformed databaseinteraction statement.
 9. The product of claim 1, wherein the primarydatabase interaction statement is received from periodically scanning adata set to identify database interaction statements.
 10. A computersystem (CS) comprising: a processor(s) set; a machine readable storagedevice; and computer code stored on the machine readable storage device,with the computer code including instructions for causing theprocessor(s) set to perform operations including the following:receiving a primary database interaction statement in SQL, determiningthat the primary database interaction statement includes at least oneSQL case statement portion, including a first SQL case statementportion, the first SQL case statement portion is eligible for an SQLquery optimization transformation based, at least in part, on comparingthe first SQL case statement portion against a set of SQL transformationpatterns, where a first SQL transformation pattern is an SQL casestatement which evaluates an equation with a constant-returning functionon one side of the equation, and a second SQL transformation pattern isan SQL case statement which evaluates an equation with a constant valueon one side of the equation, responsive to determining that the firstSQL case statement portion comparison yielded a match with the first SQLtransformation pattern, generating, by machine logic, a first set ofintermediate SQL database interaction statements based, at least inpart, on the first SQL case statement portion and a set of SQLintermediate statement guidelines corresponding to the set of SQLtransformation patterns, where the set of SQL intermediate statementguidelines includes at least a first SQL intermediate statementguideline corresponding to the first SQL transformation pattern, wherethe first SQL intermediate statement guideline stipulates that, for eachgiven constant in the first SQL case statement portion, generate aselect case statement which returns an integer value of 1 if the givenconstant is equal to output of the constant-returning function and avalue of 0 if they are not equal, responsive to generating at least oneSQL intermediate statement based upon the first SQL intermediatestatement guideline and the first SQL case statement portion, for eachgiven SQL intermediate statement of the first set of intermediate SQLinteraction statements, determining, by machine logic, the results ofthe given SQL intermediate statement of the first set of intermediateSQL interaction statements, selecting, by machine logic, a first SQLintermediate statement transformation indicator from a set of SQLintermediate statement indicators based, at least in part, on thedetermined results of the at least one SQL intermediate statement of thefirst set of intermediate SQL interaction statements, where the firstSQL intermediate statement transformation indicator indicates replacingthe first SQL case statement portion of the primary database interactionstatement with a count function multiplied by a numerical value, wherethe numerical value is based upon the determined results of the at leastone SQL intermediate statement of the first set of intermediate SQLinteraction statements, and transforming the primary databaseinteraction statement into a transformed database interaction, where thetransformed database interactions statement includes replacement of thefirst SQL case statement portion of the primary database interactionstatement with a first transformed SQL query portion that is based onthe selected first SQL intermediate transformation indicator, whereexecution of the transformed database interaction statement requiresreduced computation resources relative to the primary databaseinteraction statement.
 11. The CS of claim 10, the computer code furtherincludes instructions for causing the processor(s) set to perform thefollowing operations: communicating the transformed database interactionstatement to a database system in place of the primary databaseinteraction statement.
 12. The CS of claim 11, wherein the computer codefurther includes instructions for causing the processor(s) set toperform the following operations: performing, by the database system,the transformed database interaction statement on a database.
 13. The CSof claim 10, wherein: the primary database interaction statementincludes a second SQL case statement portion; and the transformeddatabase interaction statement includes the second SQL case statementportion.
 14. The CS of claim 10, wherein the set of SQL intermediatestatement guidelines corresponding to the set of SQL transformationpatterns includes a second SQL intermediate statement guidelinecorresponding to the second SQL transformation pattern; and the secondSQL intermediate statement guideline stipulates that, for each givenconstant in the second SQL case statement portion, generate a selectstatement which returns an integer value of 1 if the given constant isequal to output of the constant value.
 15. The CS of claim 14, whereinthe computer code further includes instructions for causing theprocessor(s) set to perform the following operations: determining thatthe second SQL case statement portion is eligible for an SQL queryoptimization transformation based, at least in part, on comparing thesecond SQL case statement portion against the set of SQL transformationpatterns; and responsive to determining that the second SQL casestatement portion comparison yielded a match with the second SQLtransformation pattern, generating, by machine logic, a second set ofintermediate SQL database interaction statements based, at least inpart, on the second SQL case statement portion and the set of SQLintermediate statement guidelines corresponding to the set of SQLtransformation patterns; responsive to generating at least one SQLintermediate statement based upon the second SQL intermediate statementguideline and the second SQL case statement portion, for each given SQLintermediate statement of the second set of intermediate SQL databaseinteraction statements, determining, by machine logic, the results ofthe given SQL intermediate statement of the second set of intermediateSQL database interaction statements; selecting, by machine logic, asecond SQL intermediate statement transformation indicator from the setof SQL intermediate statement indicators based, at least in part, on thedetermined results of at least one SQL intermediate statement of thesecond set of intermediate SQL database interaction statements, wherethe second SQL intermediate statement transformation indicator indicatesreplacing the second SQL case statement portion of the transformeddatabase interaction statement with a count function multiplied by anumerical value, where the numerical value is based upon the determinedresults of the at least one SQL intermediate statement based upon thesecond SQL intermediate statement guideline, and transforming thetransformed database interaction statement into a second transformeddatabase interaction, where the second transformed database interactionsstatement includes replacement of the second SQL case statement portionof the transformed database interaction statement with a secondtransformed SQL query portion that is based on the selected second SQLintermediate transformation indicator, where execution of the secondtransformed database interaction statement requires reduced computationresources relative to the primary database interaction statement and thetransformed database interaction statement.
 16. The CS of claim 15,wherein: the primary database interaction statement includes a third SQLcase statement portion; and the second transformed database interactionstatement includes the third SQL case statement portion.
 17. The CS ofclaim 16, wherein the computer code further includes instructions forcausing the processor(s) set to perform the following operations:determining that the third SQL case statement portion is eligible for anSQL query optimization transformation based, at least in part, oncomparing the third SQL case statement portion against the set of SQLtransformation patterns; and responsive to determining that the thirdSQL case statement portion comparison yielded a match with at least oneSQL transformation pattern, generating, by machine logic, a third set ofintermediate SQL database interaction statements based, at least inpart, on the third SQL case statement portion and the set of SQLintermediate statement guidelines corresponding to the set of SQLtransformation patterns; responsive to generating at least one SQLintermediate statement based upon at least one SQL intermediatestatement guideline and the third SQL case statement portion, for eachgiven SQL intermediate statement the third set of intermediate SQLdatabase interaction statements, determining, by machine logic, theresults of the given SQL intermediate statement of the third set ofintermediate SQL database interaction statements; responsive todetermining that the results of the third set of intermediate SQLdatabase interaction statements indicates a numerical value of zero,selecting, by machine logic, a third SQL intermediate statementtransformation indicator from the set of SQL intermediate statementindicators based, at least in part, on the determined results of atleast one intermediate SQL database interaction statement of the thirdset of intermediate SQL database interaction statements, where the thirdSQL intermediate statement transformation indicator indicates replacingthe third SQL case statement portion of the second transformed databaseinteraction statement with a numerical value of zero, and transformingthe second transformed database interaction statement into a thirdtransformed database interaction, where the third transformed databaseinteractions statement includes replacement of the third SQL casestatement portion of the second transformed database interactionstatement with a third transformed SQL query portion that is based onthe selected third SQL intermediate transformation indicator, whereexecution of the third transformed database interaction statementrequires reduced computation resources relative to: (i) the primarydatabase interaction statement, (ii) the transformed databaseinteraction statement, and (iii) the second transformed databaseinteraction statement.
 18. The CS of claim 10, wherein the primarydatabase interaction statement is received from periodically scanning adata set to identify database interaction statements.